package www.lagou.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Properties;

/**
 * 实现初始化 Druid 连接池，并初始化数据库的操作（包括清除、建表、插初始数据）。
 */
public class DruidUtils {
    /**
     * Druid 配置文件名。
     */
    private static final String DRUID_CONFIG = "druid.properties";

    /**
     * 数据库初始化时，要批量执行的语句。
     */
    private static final String[] INIT_SQL = {
            // 第一题
            "DROP TABLE IF EXISTS account",
            "CREATE TABLE account(id INT(11)  PRIMARY KEY AUTO_INCREMENT, username VARCHAR(100), card VARCHAR(100), balance DOUBLE)",
            "INSERT INTO account(id, username, card, balance) VALUES (1, 'tom', '1122334455', 20000.00), (2, 'lucy', '55443332211', 10000.00)",
            "DROP TABLE IF EXISTS `transaction`",
            "CREATE TABLE `transaction`(id INT PRIMARY KEY AUTO_INCREMENT, cardid VARCHAR(100), tratype VARCHAR(100), tramoney DOUBLE, tradate DATETIME)",

            // 第二题
            "DROP TABLE IF EXISTS phone",
            "CREATE TABLE phone(id INT PRIMARY KEY  AUTO_INCREMENT, pname VARCHAR(20), price DOUBLE, prodate DATE, color VARCHAR(20))",
            "INSERT INTO phone(id, pname, price, prodate, color) VALUES " +
            "   (1, 'IPhone11', 7800.00, '2019-07-20', '土豪金'), " +
            "   (2, '荣耀6X', 5689.00, '2018-02-12', '白色'), " +
            "   (3, '诺基亚3', 5699.00, '2011-12-05', '银白色'), " +
            "   (4, '红米6', 599.00, '2017-01-18', '香槟金'), " +
            "   (5, 'IPhoneX', 5800.00, '2018-01-18', '日落黑')",

            // 第三题
            "DROP TABLE IF EXISTS employee",
            "DROP TABLE IF EXISTS dept",
            "CREATE TABLE dept(id INT PRIMARY KEY AUTO_INCREMENT, deptname VARCHAR(20) DEFAULT NULL)",
            "CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR (30), age DOUBLE, sex VARCHAR (6), salary DOUBLE, empdate DATE, did INT, FOREIGN KEY (did) REFERENCES dept (id))",
            "INSERT INTO dept(id, deptname) VALUES (1, '销售部'), (2, '财务部'), (3, '人事部')",
            "INSERT INTO employee VALUES " +
            "   (1, '奥利给', 35, '男', 35000, '2000-10-07', 1), " +
            "   (2, '小汉堡', 32, '女', 38000, '2005-11-12', 2), " +
            "   (3, '哈拉少', 30, '男', 32000, '2008-03-14', 2), " +
            "   (4, '广坤', 40, '男', 36000, '2007-09-18', NULL)",
    };

    /**
     * 数据库连接池。
     */
    private static final DataSource POOL;

    static {
        // 初始化数据库连接池。
        DataSource dataSource = null;
        try {
            Properties config = new Properties();
            config.load(DruidUtils.class.getClassLoader().getResourceAsStream(DRUID_CONFIG));
            dataSource = DruidDataSourceFactory.createDataSource(config);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            POOL = dataSource;
        }

        // 初始化数据库。
        try {
            initializeTables();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private DruidUtils() {
    }

    /**
     * 初始化数据库，清除旧表、建立新表、插入初始数据。
     *
     * @throws SQLException 初始化语句执行出错。
     */
    private static void initializeTables() throws SQLException {
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            for (String sql : INIT_SQL) {
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接池。
     *
     * @return 数据库连接池。
     * @throws SQLException 数据库连接池未正确初始化。
     */
    public static DataSource getDataSource() throws SQLException {
        if (POOL == null) {
            throw new SQLException("cannot initialize druid");
        }
        return POOL;
    }

    /**
     * 获取数据库链接。
     *
     * @return 数据库链接。
     * @throws SQLException 无法获取数据库链接，或者数据库连接池未正确初始化。
     */
    public static Connection getConnection() throws SQLException {
        return getDataSource().getConnection();
    }
}
